package com.xiaowei.gym.gymjsp.ware.dao.impl;

import com.mysql.cj.util.StringUtils;
import com.xiaowei.gym.gymjsp.ware.dao.AdminDao;
import com.xiaowei.gym.gymjsp.ware.dto.AdminDTO;
import com.xiaowei.gym.gymjsp.ware.entity.AdminEntity;
import com.xiaowei.gym.gymjsp.ware.util.JdbcUtil;
import com.xiaowei.gym.gymjsp.ware.vo.AdminVo;
import org.apache.poi.ss.formula.functions.T;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AdminDaoImpl implements AdminDao{


    @Override
    public int createAdmin(AdminEntity adminEntity) {
        int i = 0;
        String sql = "INSERT INTO gym_admin(username,password) VALUES(?,?)";
        Object[] params = {adminEntity.getUsername(),adminEntity.getPassword()};
        try {
            i = JdbcUtil.executeUpdate(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return i;
    }

    @Override
    public int getAdminCount() {
        int count = 0;
        String sql = "select count(1) from gym_admin";
        try {
            count = JdbcUtil.executeQuery(sql, rs -> {
                int counts = 0;
                if (rs.next()) {
                    counts = rs.getInt("count(1)");
                }
                return counts;
            }, null);
        }catch (Exception exception){
            exception.printStackTrace();
        }
        return count;
    }

    @Override
    public AdminEntity validate(String username, String password) {
        String sql = "SELECT * FROM gym_admin WHERE username = ? AND password = ?";
        AdminEntity admin = new AdminEntity();
        Object[] params={username,password};
        System.out.println("dao的params:"+params.toString());
        try {
            admin = JdbcUtil.executeQuery(sql, resultSet -> {
                AdminEntity adminEntity = new AdminEntity();
                if (resultSet.next()){
                    adminEntity = new AdminEntity();
                    adminEntity.setId(resultSet.getInt("id"));
                    adminEntity.setUsername(resultSet.getString("username"));
                    adminEntity.setPassword(resultSet.getString("password"));
                }
                return adminEntity;
            }, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return admin;
    }

    @Override
    public List<AdminEntity> getAdminList(String queryUserName, int currentPageNo, int pageSize) {
        List<AdminEntity> adminEntities = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from gym_admin ");
        List<Object> list = new ArrayList<>();
        if (!StringUtils.isNullOrEmpty(queryUserName)){
            sql.append("where username like ?");
            list.add("%"+queryUserName+"%");
        }
        //在mysql数据库中，分页使用 limit startIndex，pageSize ; 总数
        sql.append(" order by createtime DESC limit ?,?");
        currentPageNo = (currentPageNo - 1) * pageSize;
        list.add(currentPageNo);
        list.add(pageSize);
        Object[] params = list.toArray();
        System.out.println("sql ----> " + sql.toString());
        try {
            List<AdminEntity> adminEntityList = new ArrayList<>();
            adminEntities = JdbcUtil.executeQuery(sql.toString(), rs -> {
                while (rs.next()) {
                    AdminEntity adminEntity = new AdminEntity();
                    adminEntity.setUsername(rs.getString("username"));
                    adminEntity.setId(rs.getInt("id"));
                    adminEntity.setPassword(rs.getString("password"));
                    adminEntity.setPhone(rs.getString("phone"));
                    adminEntity.setEmail(rs.getString("email"));
                    adminEntity.setCreatetime(rs.getTimestamp("createtime"));
                    adminEntity.setUpdatetime(rs.getTimestamp("updatetime"));
                    adminEntityList.add(adminEntity);
                }
                return adminEntityList;
            }, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return adminEntities;
    }

    @Override
    public AdminEntity getAdminById(Integer id) {
        String sql = "SELECT id,username,`password`,createtime,updatetime FROM gym_admin WHERE id = ?";
        AdminEntity result = null;
        try {
            result = JdbcUtil.executeQuery(sql, rs -> {
                AdminEntity adminEntity = new AdminEntity();
                if (rs.next()) {
                    adminEntity.setId(rs.getInt("id"));
                    adminEntity.setUsername(rs.getString("username"));
                    adminEntity.setPassword(rs.getString("password"));
                    adminEntity.setPhone(rs.getString("phone"));
                    adminEntity.setEmail(rs.getString("email"));
                    adminEntity.setCreatetime(rs.getTimestamp("createtime"));
                    adminEntity.setUpdatetime(rs.getTimestamp("updatetime"));
                }
                return adminEntity;
            }, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return result;
    }

    @Override
    public Integer modify(AdminEntity adminEntity) {
        String sql = "UPDATE gym_admin SET username = ?,password = ?,updatetime = ? WHERE id = ?";
        Object[] params = {adminEntity.getUsername(),adminEntity.getPassword(),adminEntity.getUpdatetime(),adminEntity.getId()};
        int i = 0;
        try {
            i = JdbcUtil.executeUpdate(sql,params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int[] deleteAdminByIds(Integer[] delId) {
        String sql = "DELETE FROM gym_admin WHERE id = ?";
        Object[] params = delId;
        System.out.println(params);
        int i[] = null;
        try {
                i = JdbcUtil.batchDelete(sql, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public Map<String, Object> validAdmin(AdminDTO adminDTO) {
        //在这里提供两种解决方法，一种是三个字段分贝查数据库，总共查三次数据库，
        // 另一种是利用一次sql查询，并且嵌套三个子查询，这样jdbc只需要操作一次数据库即可。
//        String sql = "SELECT count(1) FROM gym_admin WHERE username = ?";
        StringBuffer sql = new StringBuffer();
        //验证三个字段是否重复的标识,new一个map。
//        Integer[] integers1 = new Integer[3];
        Map<String,Object> map = new HashMap<>();
        //现在开始第一种方法实现
//        map = FirstMethod(adminDTO, sql);
        //开始第二种方法实现
        map = SecondMethod(adminDTO, sql);
        return map;
    }

    @Override
    public List<AdminVo> selectByPage(int offset, int pageSize) {
        String sql = "SELECT `id`,`username`, `password`, `createtime`, `updatetime` FROM gym_admin LIMIT ?,?";
        Object[] params = {offset,pageSize};
        List<AdminVo> adminVos = null;
        try {
            adminVos = JdbcUtil.executeQuery(sql, rs -> {
                List<AdminVo> adminVoList = new ArrayList<>();
                while (rs.next()) {
                    AdminVo adminVo = new AdminVo();
                    adminVo.setId(rs.getInt("id"));
                    adminVo.setUsername(rs.getString("username"));
                    adminVo.setPassword(rs.getString("password"));
                    adminVo.setCreatetime(rs.getTimestamp("createtime"));
                    adminVo.setUpdatetime(rs.getTimestamp("updatetime"));
                    adminVoList.add(adminVo);
                }
                return adminVoList;
            }, params);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return adminVos;
    }

    private Map<String,Object> FirstMethod(AdminDTO adminDTO, StringBuffer sql) {
        HashMap<String, Object> map = new HashMap<>();
        try {
            //第一次查数据库判断username是否重复
            if (!StringUtils.isNullOrEmpty(adminDTO.getName())){
                sql.append("SELECT count(1) FROM gym_admin WHERE username = ?");
                Integer integer = JdbcUtil.executeQuery(sql.toString(), rs -> {
                    Integer exists = 0;
                    if (rs.next()) {
                        exists = rs.getInt("count(1)");
                    }
                    return exists;
                }, adminDTO.getName());
                if (integer > 0){
                    map.put("name",integer);
                }else {
                    map.put("name",0);
                }
                //清空sql对象内容
                sql.setLength(0);
            }
            //再次查数据库，判断手机号是否重复
            if (!StringUtils.isNullOrEmpty(adminDTO.getPhone())){
                sql.append("SELECT count(1) FROM gym_admin WHERE phone = ?");
                Integer integer = JdbcUtil.executeQuery(sql.toString(), rs -> {
                    Integer exists = 0;
                    if (rs.next()) {
                        exists = rs.getInt("count(1)");
                    }
                    return exists;
                }, adminDTO.getPhone());
                if (integer > 0){
                    map.put("phone",integer);
                }else {
                    map.put("phone",0);
                }
                //清空sql对象内容
                sql.setLength(0);
            }
            //第三次查数据库，判断邮箱是否重复
            if (!StringUtils.isNullOrEmpty(adminDTO.getEmail())){
                sql.append("SELECT count(1) FROM gym_admin WHERE email = ?");
                Integer integer = JdbcUtil.executeQuery(sql.toString(), rs -> {
                    Integer exists = 0;
                    if (rs.next()) {
                        exists = rs.getInt("count(1)");
                    }
                    return exists;
                }, adminDTO.getEmail());
                if (integer > 0){
                    map.put("email",integer);
                }else {
                    map.put("email",0);
                }
                //清空sql对象内容
                sql.setLength(0);
            }
        }catch (SQLException throwables){
            throwables.printStackTrace();
        }
        return map;
    }

    private Map<String,Object> SecondMethod(AdminDTO adminDTO, StringBuffer sql) {
        Map<String,Object> map = new HashMap<>();
        ArrayList<Object> objects = new ArrayList<>();
        Integer[] integers = new Integer[3];
        if (!StringUtils.isNullOrEmpty(adminDTO.getName()) && !StringUtils.isNullOrEmpty(adminDTO.getPhone()) && !StringUtils.isNullOrEmpty(adminDTO.getEmail())){
            sql.append("SELECT ")
                    .append("CASE ")
                    .append("WHEN EXISTS (SELECT 1 FROM gym_admin WHERE username = ?")
                    .append(") THEN 1 ELSE 0 END AS name_exists, ")
                    .append("CASE ")
                    .append("WHEN EXISTS (SELECT 1 FROM gym_admin WHERE phone = ?")
                    .append(") THEN 1 ELSE 0 END AS phone_exists, ")
                    .append("CASE ")
                    .append("WHEN EXISTS (SELECT 1 FROM gym_admin WHERE email = ?")
                    .append(") THEN 1 ELSE 0 END AS email_exists");
            objects.add(adminDTO.getName());
            objects.add(adminDTO.getPhone());
            objects.add(adminDTO.getEmail());
        }else if (!StringUtils.isNullOrEmpty(adminDTO.getName()) || !StringUtils.isNullOrEmpty(adminDTO.getPhone()) || !StringUtils.isNullOrEmpty(adminDTO.getEmail())) {
            // 使用 PreparedStatement 进行参数绑定
            sql.setLength(0);
            sql.append("SELECT ");
            // 判断属性值是否为非空，动态拼接 SQL 语句和查询字段
            if (!StringUtils.isNullOrEmpty(adminDTO.getName())) {
                sql.append("CASE WHEN EXISTS (SELECT 1 FROM gym_admin WHERE username = ?) THEN 1 ELSE 0 END AS name_exists, ");
                objects.add(adminDTO.getName());
            } else {
                sql.append("0 AS name_exists, ");
            }
            if (!StringUtils.isNullOrEmpty(adminDTO.getPhone())) {
                sql.append("CASE WHEN EXISTS (SELECT 1 FROM gym_admin WHERE phone = ?) THEN 1 ELSE 0 END AS phone_exists, ");
                objects.add(adminDTO.getPhone());
            } else {
                sql.append("0 AS phone_exists, ");
            }
            if (!StringUtils.isNullOrEmpty(adminDTO.getEmail())) {
                sql.append("CASE WHEN EXISTS (SELECT 1 FROM gym_admin WHERE email = ?) THEN 1 ELSE 0 END AS email_exists ");
                objects.add(adminDTO.getEmail());
            } else {
                sql.append("0 AS email_exists ");
            }
        }
        try {
            integers = JdbcUtil.executeQuery(sql.toString(), rs -> {
                Integer[] values = new Integer[3];
                while (rs.next()) {
                    values[0] = rs.getInt("name_exists");
                    values[1] = rs.getInt("phone_exists");
                    values[2] = rs.getInt("email_exists");
                }
                return values;
            },objects.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        map.put("name",integers[0]);
        map.put("phone",integers[1]);
        map.put("email",integers[2]);
        return map;
    }
}
